﻿using com.iot.core.annotation; 
using com.iot.core.data;  
using Microsoft.AspNetCore.Mvc;   
using com.iot.core.manager.id;
using System.Data; 
using com.iot.core.util; 
using com.iot.ht.Controllers.htsql.data; 
using com.iot.core.exception;
 
using Microsoft.EntityFrameworkCore;
using com.iot.core.boot;
using com.iot.core.infrastructure;
using com.iot.ht.Controllers.htsoft.data;
using System.Data.SqlClient;
using CSRedis;
using MySql.Data.MySqlClient;
using System.Net.Http;

namespace com.iot.ht.Controllers.htsql
{
    [ApiController]
    [Route("[controller]/[action]")]
    [Tag(Name = "Sql维护", Description = "")]
    public class HtSqlController : ControllerBase
    {
        IRepository<HtSqlGroup> baseMapper;
        IRepository<HtSqlItem> htSqlItemMapper;
        IRepository<HtSqlRun> htSqlRunMapper;
        public HtSqlController(IRepository<HtSqlGroup> p1,
        IRepository<HtSqlItem> p2,
        IRepository<HtSqlRun> p3 )
        {
            baseMapper = p1;
            htSqlItemMapper = p2;
            htSqlRunMapper = p3;
        }
        [HttpPost]
        [Operation(Summary = "树形结构", Description = "")]
        [Auth(AuthType.Login)]
        [Log(LogNeed = false, LogPin = true, LogPout = true, LogTemplete = "")]
        public async Task<List<TreeViewNode>> Tree()
        {
            var lgroup =await baseMapper.GetAll()
                .AsNoTracking()
                .OrderBy(a => a.Sortno)
                .ToListAsync();
            var litem = await htSqlItemMapper.GetAll()
                .AsNoTracking()
                .ToListAsync();
            var r=new List<TreeViewNode>();
            foreach ( var mgroup in lgroup )
            {
                var tngroup = new TreeViewNode
                {
                    Id = mgroup.Id,
                    Name = mgroup.Name,
                    Code = mgroup.Lx,
                    Lx = "目录",
                    Expanded = true,
                    Imgurl = "layui-icon-template-one",
                    Pid = 0,

                };
                var lgroupitem=litem.Where(a=>a.HtsqlgroupId== mgroup.Id)
                    .OrderBy(a=>a.Sortno)
                    .ToList(); 
                foreach(var mitem in lgroupitem)
                {
                    var tnitem = new TreeViewNode
                    {
                        Id=mitem.Id,
                        Name=mitem.Name,
                        Code= mitem.Lx,
                        Lx="菜单",
                        Imgurl= "layui-icon-app",
                        Pid=mgroup.Id
                    };
                    tngroup.Children.Add(tnitem);
                }
                r.Add(tngroup );    
            }
            return r;
        }

        [HttpPost]
        [Operation(Summary = "分组详情", Description = "")]
        [Auth(AuthType.Login)]
        [Log(LogNeed = false, LogPin = true, LogPout = true, LogTemplete = "")]
        public async  Task<HtSqlGroup> DetailAsync(BaseQto mm)
        {
            var m =await baseMapper.GetByIdAsync(mm.Id);
            return m;
        }

        [HttpPost]
        [Operation(Summary = "Sql详情", Description = "")]
        [Auth(AuthType.Login)]
        [Log(LogNeed = false, LogPin = true, LogPout = true, LogTemplete = "")]
        public async Task<HtSqlItem> DetailItemAsync(BaseQto mm)
        {
            var m = await htSqlItemMapper.GetByIdAsync(mm.Id);
            return m;
        }


        [HttpPost]
        [Operation(Summary = "添加Sql分组初始化", Description = "")]
        [Auth(AuthType.Login)]
        [Log(LogNeed = false, LogPin = true, LogPout = true, LogTemplete = "")]
        public async Task<HtSqlGroup> InsertInitAsync()
        {
            var Sortno = await baseMapper.GetAll()
                .MaxAsync(a => (long?)a.Sortno)
                ;

            var mm = new HtSqlGroup
            {
                Id = HtIdManager.Current.NextLong(),
                Lx = "MySql",
                Sortno=10
            };
            if (Sortno.HasValue)
            {
                mm.Sortno = Sortno.Value + 10;
            }
            return mm;
        }


        [HttpPost]
        [Operation(Summary = "添加Sql分组", Description = "")]
        [Auth(AuthType.Auth)]
        [Log(LogNeed = true, LogPin = true, LogPout = true, LogTemplete = "")]
        public async Task<int> Insert(HtSqlGroup mm)
        {
            baseMapper.Insert(mm);
            return await baseMapper.SaveChagnesAsync();
        }

        [HttpPost]
        [Operation(Summary = "修改Sql分组", Description = "")]
        [Auth(AuthType.Auth)]
        [Log(LogNeed = true, LogPin = true, LogPout = true, LogTemplete = "")]
        public async Task<int> Update(HtSqlGroup mm)
        {
            baseMapper.Update(mm);
            return await baseMapper.SaveChagnesAsync();
        }

        [HttpPost]
        [Operation(Summary = "删除Sql分组", Description = "")]
        [Auth(AuthType.Auth)]
        [Log(LogNeed = true, LogPin = true, LogPout = true, LogTemplete = "")]
        public async Task<int> Delete(BaseQto mm)
        {
            var m =await baseMapper.GetByIdAsync(mm.Id);
            baseMapper.Delete(m);
            return await baseMapper.SaveChagnesAsync();
        }





        [HttpPost]
        [Operation(Summary = "添加Sql项目初始化", Description = "")]
        [Auth(AuthType.Login)]
        [Log(LogNeed = false, LogPin = true, LogPout = true, LogTemplete = "")]
        public async Task<HtSqlItem> InsertInitItemAsync(BaseQto qm)
        {
            var Sortno = await htSqlItemMapper.GetAll()
                .Where(a=>a.HtsqlgroupId==qm.Id)
                .MaxAsync(a => (long?)a.Sortno)
                ;

            var mm = new HtSqlItem
            {
                Id = HtIdManager.Current.NextLong(),
                Lx = "查询",
                Sortno = 10,
                HtsqlgroupId=qm.Id
            };
            if (Sortno.HasValue)
            {
                mm.Sortno = Sortno.Value + 10;
            }
            return mm;
        }


        [HttpPost]
        [Operation(Summary = "添加Sql项目", Description = "")]
        [Auth(AuthType.Auth)]
        [Log(LogNeed = true, LogPin = true, LogPout = true, LogTemplete = "")]
        public async Task<int> InsertItem(HtSqlItem mm)
        {
            htSqlItemMapper.Insert(mm);
            return await htSqlItemMapper.SaveChagnesAsync();
        }

        [HttpPost]
        [Operation(Summary = "修改Sql项目", Description = "")]
        [Auth(AuthType.Auth)]
        [Log(LogNeed = true, LogPin = true, LogPout = true, LogTemplete = "")]
        public async Task<int> UpdateItem(HtSqlItem mm)
        {
            htSqlItemMapper.Update(mm);
            return await htSqlItemMapper.SaveChagnesAsync();
        }

        [HttpPost]
        [Operation(Summary = "删除Sql项目", Description = "")]
        [Auth(AuthType.Auth)]
        [Log(LogNeed = true, LogPin = true, LogPout = true, LogTemplete = "")]
        public async Task<int> DeleteItem(BaseQto mm)
        {
            var m = await htSqlItemMapper.GetByIdAsync(mm.Id);
            htSqlItemMapper.Delete(m);
            return await htSqlItemMapper.SaveChagnesAsync();
        }


        [HttpPost]
        [Operation(Summary = "运行Sql", Description = "")]
        [Auth(AuthType.Auth)]
        [Log(LogNeed = true, LogPin = true, LogPout = true, LogTemplete = "")]
        public async Task<object> Run(IdNameAttrDto mm)
        {
            var mitem = await htSqlItemMapper.GetByIdAsync(mm.Id);
            var mgroup = await baseMapper.GetByIdAsync(mitem.HtsqlgroupId);
            if (!string.IsNullOrWhiteSpace(mitem.P1Name) && string.IsNullOrWhiteSpace(mm.P1))
            {
                throw new MyException("参数1不能为空");
            }

            if (!string.IsNullOrWhiteSpace(mitem.P2Name) && string.IsNullOrWhiteSpace(mm.P2))
            {
                throw new MyException("参数2不能为空");
            }

            if (!string.IsNullOrWhiteSpace(mitem.P3Name) && string.IsNullOrWhiteSpace(mm.P3))
            {
                throw new MyException("参数3不能为空");
            }

            if (!string.IsNullOrWhiteSpace(mitem.P4Name) && string.IsNullOrWhiteSpace(mm.P4))
            {
                throw new MyException("参数4不能为空");
            }



            var BkSql = mitem.BkSql
                .Replace("{P1}", mm.P1)
                .Replace("{P2}", mm.P2)
                .Replace("{P3}", mm.P3)
                .Replace("{P4}", mm.P4)
                ;
            var RunSql = mitem.RunSql
                .Replace("{P1}", mm.P1)
                .Replace("{P2}", mm.P2)
                .Replace("{P3}", mm.P3)
                .Replace("{P4}", mm.P4)
                ;
            var mrun = new HtSqlRun
            {
                Id = HtIdManager.Current.NextLong(),
                HtsqlitemId = mitem.Id,
                P1 = mm.P1,
                P2 = mm.P2,
                P3 = mm.P3,
                P4 = mm.P4,
                BkSql = BkSql,
                RunSql = RunSql,
                BkData = "",
                 
            };

            var dtsart = DateTime.Now;

            if (mitem.Lx == "查询")
            {
                var dst = Query(mgroup, RunSql);
                mrun.Mseconds = Convert.ToInt32((DateTime.Now - dtsart).TotalMilliseconds);
                mrun.RowCount = dst.Tables[0].Rows.Count;
                htSqlRunMapper.Insert(mrun);
                await htSqlRunMapper.SaveChagnesAsync();
                return dst.Tables[0];
            }
            else
            {
                if (!string.IsNullOrEmpty(BkSql))
                {
                    var dst = Query(mgroup, BkSql);
                    var BkData = MyJSON.ToJSONString(dst);
                    if (BkData.Length > 2000)
                    {
                        //文件太大
                        await FileUtil.SaveAsync("htsqlrun", mitem.Id.ToString() + ".txt", BkData);
                        mrun.BkData = BkData.Substring(0, 2000);
                    }
                    else
                    {
                        mrun.BkData = MyJSON.ToJSONString(dst);
                    }                    
                }

                mrun.RowCount = ExecuteNonquery(mgroup, RunSql);
                mrun.Mseconds = Convert.ToInt32((DateTime.Now - dtsart).TotalMilliseconds);
              
                htSqlRunMapper.Insert(mrun);
                await htSqlRunMapper.SaveChagnesAsync();
                return mrun.RowCount;
            }
             
        }


        [HttpPost]
        [Operation(Summary = "Sql运行分页", Description = "")]
        [Auth(AuthType.Login)]
        [Log(LogNeed = false, LogPin = false, LogPout = false, LogTemplete = "")]
        public async Task<Page<HtSqlRun>> PageRunAsync([FromBody] BasePageDtQto mm)
        {
            var q = htSqlRunMapper.GetAll();
            q = q.Where(a => a.HtsqlitemId == mm.Id);
            if (!string.IsNullOrWhiteSpace(mm.Dtb))
            {
                q = q.Where(a => a.CrtDt >= mm.GetDB());
            }
            if (!string.IsNullOrWhiteSpace(mm.Dte))
            {
                q = q.Where(a => a.CrtDt < mm.GetDE());
            }
            
            var r = await htSqlRunMapper.PageAsync(q, mm);
            return r;
        }

         



        DataSet Query(HtSqlGroup mgroup,string sql)
        {
            switch (mgroup.Lx)
            {
                case "SqlServer":
                    return SqlServerHelper.Query(mgroup.ConnString, sql);
                case "MySql":
                    return core.util.MysqlHelper.Query(mgroup.ConnString, sql);
                case "SqlLite":
                    return SqlLiteHelper.Query(mgroup.ConnString, sql);
                default:
                    throw new MyException("暂时不支持的类型");
            }
        }

        int ExecuteNonquery(HtSqlGroup mgroup, string sql)
        {
            switch (mgroup.Lx)
            {
                case "SqlServer":
                    return SqlServerHelper.ExecuteNonquery(mgroup.ConnString, sql);
                case "MySql":
                    return core.util.MysqlHelper.ExecuteNonquery(mgroup.ConnString, sql);
                case "SqlLite":
                    return SqlLiteHelper.ExecuteNonquery(mgroup.ConnString, sql);
                default:
                    throw new MyException("暂时不支持的类型");
            }
        }

    }
}
